/**
 * 数据库迁移脚本：为 UserSnapshot 表添加管理员权限字段
 * 
 * 使用方法：
 * node scripts/migrate-admin-fields.js
 */

// 由于是 TypeScript 文件，需要使用 ts-node 或编译后的 JS 文件
// 这里直接创建 Sequelize 连接
const { Sequelize } = require('sequelize');

// 创建数据库连接
const sequelize = new Sequelize('cc', 'cc', '7898ikuojl', {
  host: '47.117.120.183',
  port: 5432,
  dialect: 'postgres',
  logging: false, // 关闭日志以保持输出清洁
  timezone: '+08:00',
  dialectOptions: {
    timezone: '+08:00',
    useUTC: false,
  },
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});

async function migrateAdminFields() {
  console.log('🚀 开始添加管理员权限字段到 UserSnapshot 表...');
  
  try {
    // 确保数据库连接
    await sequelize.authenticate();
    console.log('✅ 数据库连接成功');
    
    // 执行迁移 SQL
    const migrations = [
      // 添加 isMuted 字段
      `ALTER TABLE user_snapshots ADD COLUMN IF NOT EXISTS "isMuted" BOOLEAN DEFAULT FALSE;`,
      
      // 添加 isAdmin 字段
      `ALTER TABLE user_snapshots ADD COLUMN IF NOT EXISTS "isAdmin" BOOLEAN DEFAULT FALSE;`,
      
      // 添加 isSuperAdmin 字段
      `ALTER TABLE user_snapshots ADD COLUMN IF NOT EXISTS "isSuperAdmin" BOOLEAN DEFAULT FALSE;`,
      
      // 添加 adminPrivileges 字段
      `ALTER TABLE user_snapshots ADD COLUMN IF NOT EXISTS "adminPrivileges" TEXT;`,
    ];
    
    // 执行每个迁移语句
    for (let i = 0; i < migrations.length; i++) {
      const sql = migrations[i];
      console.log(`📝 执行迁移 ${i + 1}/${migrations.length}: ${sql.substring(0, 50)}...`);
      
      try {
        await sequelize.query(sql);
        console.log(`✅ 迁移 ${i + 1} 执行成功`);
      } catch (error) {
        if (error.message.includes('already exists')) {
          console.log(`⚠️ 迁移 ${i + 1} 跳过：字段已存在`);
        } else {
          throw error;
        }
      }
    }
    
    // 创建索引
    const indexes = [
      `CREATE INDEX IF NOT EXISTS "user_snapshots_is_admin" ON "user_snapshots" ("isAdmin");`,
      `CREATE INDEX IF NOT EXISTS "user_snapshots_is_super_admin" ON "user_snapshots" ("isSuperAdmin");`,
      `CREATE INDEX IF NOT EXISTS "user_snapshots_is_muted" ON "user_snapshots" ("isMuted");`,
    ];
    
    console.log('📊 创建索引...');
    for (let i = 0; i < indexes.length; i++) {
      const sql = indexes[i];
      console.log(`📝 创建索引 ${i + 1}/${indexes.length}: ${sql.substring(0, 50)}...`);
      
      try {
        await sequelize.query(sql);
        console.log(`✅ 索引 ${i + 1} 创建成功`);
      } catch (error) {
        if (error.message.includes('already exists')) {
          console.log(`⚠️ 索引 ${i + 1} 跳过：索引已存在`);
        } else {
          throw error;
        }
      }
    }
    
    // 添加注释
    const comments = [
      `COMMENT ON COLUMN user_snapshots."isMuted" IS '用户是否被禁言';`,
      `COMMENT ON COLUMN user_snapshots."isAdmin" IS '用户是否为管理员';`,
      `COMMENT ON COLUMN user_snapshots."isSuperAdmin" IS '用户是否为超级管理员';`,
      `COMMENT ON COLUMN user_snapshots."adminPrivileges" IS '管理员权限列表（JSON字符串格式）';`,
    ];
    
    console.log('💬 添加字段注释...');
    for (let i = 0; i < comments.length; i++) {
      const sql = comments[i];
      console.log(`📝 添加注释 ${i + 1}/${comments.length}`);
      
      try {
        await sequelize.query(sql);
        console.log(`✅ 注释 ${i + 1} 添加成功`);
      } catch (error) {
        console.log(`⚠️ 注释 ${i + 1} 添加失败: ${error.message}`);
      }
    }
    
    console.log('🎉 管理员权限字段迁移完成！');
    
    // 验证字段是否添加成功
    console.log('🔍 验证字段添加情况...');
    const [results] = await sequelize.query(`
      SELECT column_name, data_type, is_nullable, column_default 
      FROM information_schema.columns 
      WHERE table_name = 'user_snapshots' 
      AND column_name IN ('isMuted', 'isAdmin', 'isSuperAdmin', 'adminPrivileges')
      ORDER BY column_name;
    `);
    
    console.log('📋 新增字段信息:');
    results.forEach(row => {
      console.log(`  - ${row.column_name}: ${row.data_type} (nullable: ${row.is_nullable}, default: ${row.column_default})`);
    });
    
  } catch (error) {
    console.error('❌ 迁移失败:', error);
    throw error;
  } finally {
    // 关闭数据库连接
    await sequelize.close();
    console.log('🔌 数据库连接已关闭');
  }
}

// 执行迁移
migrateAdminFields()
  .then(() => {
    console.log('✅ 迁移脚本执行完成');
    process.exit(0);
  })
  .catch((error) => {
    console.error('❌ 迁移脚本执行失败:', error);
    process.exit(1);
  });